package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.NbfbFour;

import java.util.List;

@Mapper
public interface ZyclFourMapper extends BaseMapper<Zycl> {

    @Select("with base as (\n" +
            "select ks.kqbh, ks.djflbh, ks.xzqdm,\n" +
            "    case when LEFT(ks.kqbh, 4) in ('1324','1326') then '130600' else  LEFT(ks.kqbh, 4) ||'00' end xzqbh,  \n" +
            "    ks.kcmc, ks.jsdxmc, \n" +
            "    ks.kcdm, ks.tjdx, zy.zycllx,\n" +
            "    case when kc.xsbs = 0 then zy.jsldw else zy.ksldw end dw,\n" +
            "    case when kc.xsbs = 0 then nmbyjsl else nmbyksl end nmby\n" +
            "from hisi_kshz ks\n" +
            "left join hisi_ks_zyclhz zy\n" +
            "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "left join kcl_cb06_kc kc on ks.kcdm=kc.kcdm and ks.tjdx = kc.tjdx\n" +
            "where ks.nd = #{nd} and ks.sbbsm<>3 and zy.zycllx IN ('TM', 'KZ', 'TD', 'ZS', 'KX') \n" +
            "),\n" +
            "\n" +
            "-- 去除其他无关字段\n" +
            "nodata as (\n" +
            "with tmp as (\n" +
            "select distinct kqbh, xzqbh, kcmc, jsdxmc, kcdm,  tjdx, dw\n" +
            "from base)\n" +
            "select kqbh, xzqbh, kcmc, case when kcdm in (42201, 42202, 32006, 63701) then '' else jsdxmc end jsdxmc, kcdm,  case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end tjdx, dw from tmp\n" +
            "),\n" +
            "\n" +
            "-- 各个市\n" +
            "head1 as (\n" +
            "select nodata.xzqbh, xzq.mc, kcmc,jsdxmc, kcdm, tjdx, max(dw) dw, count(kqbh) kqs \n" +
            "from nodata \n" +
            "left join kcl_cb08_xzqh xzq on nodata.xzqbh = xzq.dm::varchar\n" +
            "group by kcmc, jsdxmc, kcdm, tjdx, nodata.xzqbh, xzq.mc\n" +
            "order by kcdm, tjdx, nodata.xzqbh\n" +
            "),\n" +
            "\n" +
            "-- 河北省\n" +
            "head2 as (\n" +
            "select xzq.fdm, kcmc, jsdxmc, kcdm, tjdx, max(dw) dw, sum(kqs) kqs\n" +
            "from head1 \n" +
            "left join kcl_cb08_xzqh xzq on head1.xzqbh = xzq.dm::varchar\n" +
            "group by kcmc, jsdxmc, kcdm, tjdx, xzq.fdm\n" +
            "-- order by kcdm, tjdx, xzq.fdm\n" +
            "),\n" +
            "\n" +
            "head as (\n" +
            "select head2.fdm::varchar, xzq.mc, kcmc, jsdxmc, kcdm, tjdx, dw, kqs \n" +
            "from head2\n" +
            "left join kcl_cb08_xzqh xzq on head2.fdm = xzq.dm\n" +
            "union \n" +
            "select * from head1\n" +
            "),\n" +
            "\n" +
            "-- 组合数据\n" +
            "data1 as (\n" +
            "select kcdm,  case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end tjdx, xzq.fdm, \n" +
            "       sum(case when base.zycllx = 'ZS' then nmby else 0 end) zs,\n" +
            "       sum(case when base.zycllx = 'KX' then nmby else 0 end) kx,\n" +
            "\t\t\t sum(case when base.zycllx in ('ZS', 'KX') then nmby else 0 end) as clhj,\n" +
            "       sum(case when base.zycllx = 'TM' then nmby else 0 end) tm,\n" +
            "       sum(case when base.zycllx = 'KZ' then nmby else 0 end) kz,\n" +
            "       sum(case when base.zycllx = 'TD' then nmby else 0 end) td,\n" +
            "       sum(case when base.zycllx in ('TM', 'KZ', 'TD') then nmby else 0 end) zylhj\n" +
            "from base\n" +
            "left join kcl_cb08_xzqh xzq on base.xzqdm = xzq.dm::varchar\n" +
            "group by  kcdm,  case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end, xzq.fdm\n" +
            "),\n" +
            "\n" +
            "data2 as(\n" +
            "select kcdm, tjdx, xzq.fdm, sum(zs) zs, sum(kx) kx,sum(clhj) as clhj, sum(tm) tm, sum(kz) kz, sum(td) td, sum(zylhj) as zylhj\n" +
            "from data1\n" +
            "left join kcl_cb08_xzqh xzq on data1.fdm = xzq.dm\n" +
            "group by kcdm, tjdx, xzq.fdm\n" +
            "),\n" +
            "\n" +
            "\n" +
            "alldata as(\n" +
            "select * from data1\n" +
            "UNION\n" +
            "select * from data2\n" +
            ")\n" +
            "\n" +
            "select head.mc as xzqmc,(CASE  WHEN head.jsdxmc <> '' THEN CONCAT(head.kcmc, ' (', head.jsdxmc, ')')  ELSE head.kcmc END )AS kcmc, head.dw, head.kcdm,head.tjdx, head.kqs as count, cast(zs AS DECIMAL(18,2)),  cast(kx AS DECIMAL(18,2)), cast(tm AS DECIMAL(18,2)), cast(kz AS DECIMAL(18,2)), cast(td AS DECIMAL(18,2)), cast(zylhj AS DECIMAL(18,2)),cast(clhj AS DECIMAL(18,2)),(CASE WHEN head.tjdx::VARCHAR IS NOT NULL AND head.tjdx::VARCHAR  <> '' THEN CONCAT(head.kcdm::VARCHAR, '_', head.tjdx::VARCHAR) ELSE head.kcdm::VARCHAR END) AS bz\n" +
            "from head \n" +
            "left join alldata on head.kcdm=alldata.kcdm and head.tjdx=alldata.tjdx and head.fdm = alldata.fdm::varchar\n" +
            "order by head.kcdm, head.tjdx, head.fdm")
    List<NbfbFour> getCityList(@Param("nd") Integer nd);

}
